﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;


namespace ZhiAo.WebLib.Data
{

    public class DBHelper
    {
        private static ZhiAo.CoreLibrary.CommonLibrary cmn = new ZhiAo.CoreLibrary.CommonLibrary();
        public static void ExecuteNoneQuery(string sql)
        {
            DB myDB = new DB();
            try
            {
                myDB.ExecuteNonQuery(sql);
            }
            finally
            {
                myDB.Close();
            }
        }

        public static void ExecuteNoneQuery(string sql, object[] param)
        {
            DB myDB = new DB();
            try
            {
                myDB.ExecuteNonQuery(sql, param);
            }
            finally
            {
                myDB.Close();
            }
        }

        /// <summary>
        /// 当前执行过程中启动事务
        /// </summary>
        /// <param name="sql"></param>
        public static void ExecuteComplex(List<string> listSql, object ts)
        {
            bool currentTransaction = false;//当前方法启动事务
            if (ts == null)
            {
                currentTransaction = true;
                ts = TDAIClient.BusinessDB.CreateTransaction();
            }

            try
            {
                foreach (string sql in listSql)
                {
                    TDAIClient.BusinessDB.Execute(sql, true, ts);
                }

                if (currentTransaction)
                {
                    TDAIClient.BusinessDB.Commit(ts);
                }
            }
            catch (Exception ex)
            {
                if (currentTransaction)
                {
                    TDAIClient.BusinessDB.RollBack(ts);
                }
                throw ex;
            }
        }

        public static void ExecuteComplex(List<string> listSql)
        {
            ExecuteComplex(listSql, null);
        }

        public static string LoginDBUserName
        {
            get
            {
                string loginDBUserName = "";
                string conn = TDAIClient.BusinessDB.ConnectionString.ToUpper();
                string[] arrconn = conn.Split(';');
                foreach (string item in arrconn)
                {
                    string section = item.Trim().Replace(" ", "");
                    if (section.Contains("USERID"))
                    {
                        loginDBUserName = section.Split('=')[1];
                        break;
                    }
                }
                return loginDBUserName;
            }
        }


        public static DataSet GetDataSet(string sql)
        {
            DB myDB = new DB();
            try
            {
                return myDB.GetDataSet(sql);
            }
            finally
            {
                myDB.Close();
            }
        }

        public static DataTable GetDataTable(string sql)
        {
            if (GetDataSet(sql).Tables.Count > 0)
                return GetDataSet(sql).Tables[0];
            else
                return new DataTable();
        }

        public static bool Exists(string sql, object ts)
        {
            DataTable data = TDAIClient.BusinessDB.GetDataView(sql, true, ts).Table;
            return cmn.CheckEOF(data);
        }


        public static bool Exists(string sql)
        {
            DataTable data = GetDataTable(sql);
            return cmn.CheckEOF(data);
        }

        public static bool NotExists(string sql)
        {
            return !Exists(sql);
        }

        public static bool NotExists(string sql, object ts)
        {
            return !Exists(sql, ts);
        }

        public static DataView GetDataView(string sql)
        {
            return GetDataTable(sql).DefaultView;
        }


        public static DataSet GetPageDataSet(string SQL, int iPageNum, int iPerPageRow, ref int allRowCount, ref int iPageCount)
        {
            int num = (iPageNum - 1) * iPerPageRow;//起始行
            int num2 = (iPageNum) * iPerPageRow;//结束行
            DataSet ds = new DataSet();
            StringBuilder builder = new StringBuilder();
            builder.Append(" SELECT *  FROM (SELECT TEMP.*, ROWNUM TEMPID FROM ( ");
            builder.Append(SQL);
            builder.Append(" ) TEMP ");
            builder.Append(string.Format(" WHERE ROWNUM <= {0})", num2));
            builder.Append(string.Format(" WHERE TEMPID > {0}", num));
            string str = string.Format("SELECT COUNT(*) FROM ({0})", SQL);
            using (OracleConnection connection = new OracleConnection())
            {
                DB myDB = new DB();

                connection.ConnectionString = myDB.ConnectionString;
                connection.Open();
                OracleCommand command = new OracleCommand();
                command.Connection = connection;
                command.CommandText = str;
                allRowCount = Convert.ToInt32(command.ExecuteScalar());
                iPageCount = Convert.ToInt32(Math.Ceiling((double)(Convert.ToDouble((int)allRowCount) / Convert.ToDouble(iPerPageRow))));
                new OracleDataAdapter(builder.ToString(), connection).Fill(ds);

                if (ds != null)
                {
                    ds.Tables[0].Columns.Remove("TEMPID");
                }
            }
            return ds;
        }

        public static DataTable GetPageTableBySQL(string SQL, int iPageNum, int iPerPageRow, ref int allRowCount, ref int iPageCount)
        {
            if (ZhiAo.CoreLibrary.PlatformInfo.DBType == ZhiAo.CoreLibrary.DBType.Oracle)
            {
                return GetPageDataSet(SQL, iPageNum, iPerPageRow, ref allRowCount, ref iPageCount).Tables[0];
            }
            else
            {
                return GetPageTableBySQLForSQLServer(SQL, iPageCount, iPerPageRow, ref allRowCount, ref iPageCount);
            }
        }

        private static DataTable GetPageTableBySQLForSQLServer(string SQL, int iPageNum, int iPerPageRow, ref int allRowCount, ref int iPageCount)
        {
            int num = (iPageNum - 1) * iPerPageRow;
            int num2 = iPageNum * iPerPageRow;
            DataTable dataTable = new DataTable();


            SQL = SQL.Trim();
            SQL = SQL.Substring(7);

            string str = string.Format("SELECT COUNT(*) FROM ({0}) T1", "SELECT TOP 1000000000 " + SQL);

            SQL = "SELECT T2.* FROM (SELECT TOP 20 t1.* FROM ( SELECT TOP 29  " + SQL + " )t1)t2 ";

            using (SqlConnection connection = new SqlConnection())
            {
                DB myDB = new DB();

                connection.ConnectionString = myDB.ConnectionString;

                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = str;
                allRowCount = Convert.ToInt32(command.ExecuteScalar());
                iPageCount = Convert.ToInt32(Math.Ceiling((double)(Convert.ToDouble((int)allRowCount) / Convert.ToDouble(iPerPageRow))));
                new SqlDataAdapter(SQL, connection).Fill(dataTable);
            }
            return dataTable;
        }


        public static object CreateParam(string name, string value, string type)
        {
            ParameterDirection direction = ParameterDirection.Input;
            if (type == "E") direction = ParameterDirection.Output;
            return TDAIClient.BusinessDB.CreateParameter(name, value, direction, 1024 * 1024 * 1000);
        }
    }
}
